package com.huabo.contract.oracle.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblOrganization;
import com.huabo.contract.oracle.entity.TblStaff;

import java.math.BigDecimal;

public class TblStaffMapperSqlConfig {


    public String selectListByPageInfo(PageInfo<TblStaff> pageInfo, BigDecimal orgid, TblOrganization organization) {

        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (select * from TBL_ORGANIZATION org where ORGTYPE != 0 START WITH ORG.FATHERORGID = " + orgid + "  CONNECT BY PRIOR ORG.ORGID = ORG.FATHERORGID");
        if (organization.getOrgtype() != null && organization.getOrgtype().toString().equals("0")) {
            sqlSb.append("select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from TBL_STAFF sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID =" + organization.getOrgid() + " AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        sqlSb.append(" order by STAFFID desc) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String selectListByPageInfoCount(BigDecimal orgid, TblOrganization organization) {

        StringBuffer sqlSb = new StringBuffer("select count(*) from TBL_STAFF sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID=" + organization.getOrgid() + " AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        if (organization.getOrgtype() != null && organization.getOrgtype().toString().equals("0")) {
            sqlSb.append("select COUNT(*) from TBL_STAFF sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID =" + organization.getOrgid() + " AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        String sql = sqlSb.toString();
        return sql;
    }

    public String selectListByPage(PageInfo<TblStaff> pageInfo, BigDecimal orgid, TblOrganization organization) {

        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from tbl_staff sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID=" + organization.getOrgid() + " AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        if (organization.getOrgtype() != null && organization.getOrgtype().toString().equals("0")) {
            sqlSb.append("select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from TBL_STAFF sta INNER JOIN TBL_ORGANIZATION org ON STA.ORGID=ORG.ORGID  where STA.ORGID =" + organization.getOrgid() + " AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        sqlSb.append(" order by STAFFID desc) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }

    public String findByRolelOne(String rolename,String orgid){
        String sql="SELECT * FROM TBL_STAFF  WHERE ROLEID in (SELECT RID FROM TBL_ROLE where rname='"+rolename+"科长' or rname='"+rolename+"副局长' or rname='"+rolename+"处长'  or rname='"+rolename+"局长')  AND ORGID in (select ORGID from TBL_ORGANIZATION where 1=1 AND ORGTYPE=0 start with  FATHERORGID="+orgid+" connect by prior FATHERORGID= ORGID) AND rownum = 1";
        return sql.toString();
    }
    public String findByStaffManOrgs(String orgid){
    	String sql="SELECT * FROM TBL_STAFF WHERE MANAGEORGS like '%"+orgid+"%'";
    	return sql;
    }
    public String findByStaffFgOrgs(String orgid){
    	String sql="SELECT * FROM TBL_STAFF WHERE FGORGS like '%"+orgid+"%'";
    	return sql;
    }
    public String findByJobName(String jobname,String orgid){
		String sql="SELECT * FROM TBL_STAFF WHERE ORGID="+orgid+" AND JOBID in (SELECT JOBID from TBL_JOB WHERE COMPANYID in (select orgid from TBL_ORGANIZATION o where o.ORGTYPE != 0  START WITH o.ORGID = "+orgid+" CONNECT by PRIOR o.FATHERORGID = o.ORGID) AND JOBNAME like'%"+jobname+"%')";
    	return sql.toString();
    }

    public String findAllPageBeanPid(PageInfo<TblStaff> pageInfo, TblOrganization attribute){
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (select STAFFID,REALNAME,STA.ORGID,ORG.ORGNAME,STA.ADDRESS,STA.EMAIL,username from TBL_STAFF STA INNER JOIN TBL_ORGANIZATION ORG ON STA.ORGID=ORG.ORGID WHERE 1=1 ");
        if(attribute.getOrgtype()!=null && attribute.getOrgtype().toString().equals("0")){
            sqlSb.append("AND STA.ORGID ="+attribute.getOrgid()+" AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }else {
            sqlSb.append("AND STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID="+attribute.getOrgid()+" AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }

        sqlSb.append(" order by STAFFID desc) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql.toString();
    }
    public String findAllCountPageBeanPid(PageInfo<TblStaff> pageInfo, TblOrganization attribute){
        StringBuffer sqlSb = new StringBuffer("select COUNT(*) from TBL_STAFF STA INNER JOIN TBL_ORGANIZATION ORG ON STA.ORGID=ORG.ORGID WHERE 1=1 ");
        if(attribute.getOrgtype()!=null && attribute.getOrgtype().toString().equals("0")){
            sqlSb.append("AND STA.ORGID ="+attribute.getOrgid()+" AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }else {
            sqlSb.append("AND STA.ORGID in (select ORGID from TBL_ORGANIZATION org where FATHERORGID="+attribute.getOrgid()+" AND ORGTYPE=0 ) AND (STA.STATUS is NULL or STA.STATUS != 0)");
        }
        return sqlSb.toString();
    }


    public String updateTs(TblStaff ts) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_STAFF SET USERNAME = "+ts.getUsername()+" ");

        if(ts.getRealname() != null && !"".equals(ts.getRealname())) {
            sql.append(" , REALNAME = '"+ts.getRealname()+"'");
        }
        if(ts.getFixedphone() != null && !"".equals(ts.getFixedphone())) {
            sql.append(" , FIXEDPHONE = '"+ts.getFixedphone()+"'");
        }
        if(ts.getAddress() != null && !"".equals(ts.getAddress())) {
            sql.append(" , ADDRESS = '"+ts.getAddress()+"'");
        }
        if(ts.getEmail() != null && !"".equals(ts.getEmail())) {
            sql.append(" , EMAIL = '"+ts.getEmail()+"'");
        }
        if(ts.getMiblephone() != null && !"".equals(ts.getMiblephone())) {
            sql.append(" , MIBLEPHONE = '"+ts.getMiblephone()+"'");
        }
        if(ts.getMemo() != null && !"".equals(ts.getMemo())) {
            sql.append(" , MEMO = '"+ts.getMemo()+"'");
        }
        if(ts.getPassword() != null && !"".equals(ts.getPassword())) {
            sql.append(" , PASSWORD = '"+ts.getPassword()+"'");
        }
        if(ts.getJobid() != null && !"".equals(ts.getJobid())) {
            sql.append(" , JOBID = '"+ts.getJobid()+"'");
        }
        if(ts.getCreatetime() != null && !"".equals(ts.getCreatetime())) {
            sql.append(" ,CREATETIME = TO_DATE('"+ DateUtil.parseDate(ts.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(ts.getStatus() != null && !"".equals(ts.getStatus())) {
            sql.append(" , STATUS = '"+ts.getStatus()+"'");
        }

        sql.append(" WHERE staffid = '"+ts.getStaffid()+"'");
        return sql.toString();
    }


    public String saveTs(TblStaff ts) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_STAFF (STAFFID");
        StringBuffer value = new StringBuffer(" VALUES (ANALUSIS_USER_SEQUENCE.nextval");

        if(ts.getRealname() != null && !"".equals(ts.getRealname())) {
            column.append(",REALNAME");
            value.append(",'"+ts.getRealname()+"'");
        }
        if(ts.getFixedphone() != null && !"".equals(ts.getFixedphone())) {
            column.append(",FIXEDPHONE");
            value.append(",'"+ts.getFixedphone()+"'");
        }
        if(ts.getAddress() != null && !"".equals(ts.getAddress())) {
            column.append(",ADDRESS");
            value.append(",'"+ts.getAddress()+"'");
        }
        if(ts.getEmail() != null && !"".equals(ts.getEmail())) {
            column.append(",EMAIL");
            value.append(",'"+ts.getEmail()+"'");
        }
        if(ts.getMiblephone() != null && !"".equals(ts.getMiblephone())) {
            column.append(",MIBLEPHONE");
            value.append(",'"+ts.getMiblephone()+"'");
        }
        if(ts.getMemo() != null && !"".equals(ts.getMemo())) {
            column.append(",MEMO");
            value.append(",'"+ts.getMemo()+"'");
        }
        if(ts.getUsername() != null && !"".equals(ts.getUsername())) {
            column.append(",USERNAME");
            value.append(",'"+ts.getUsername()+"'");
        }
        if(ts.getPassword() != null && !"".equals(ts.getPassword())) {
            column.append(",PASSWORD");
            value.append(",'"+ts.getPassword()+"'");
        }
        if(ts.getJobid() != null && !"".equals(ts.getJobid())) {
            column.append(",JOBID");
            value.append(",'"+ts.getJobid()+"'");
        }
        if(ts.getCreatetime() != null && !"".equals(ts.getCreatetime())) {
            column.append(",CREATETIME");
            value.append(",TO_DATE('"+ DateUtil.parseDate(ts.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(ts.getStatus() != null && !"".equals(ts.getStatus())) {
            column.append(",STATUS");
            value.append(",'"+ts.getStatus()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }
    
    
    
    
    //==
    public String selectTreeListByPageInfo(PageInfo<TblStaff> pageInfo,Integer orgid) {
		StringBuffer sb = new StringBuffer("SELECT * FROM "
				+ "(SELECT T1.*,ROWNUM RN  FROM "
				+ "(SELECT TNA.*,ORG.ORGNAME ORGNAME "
				+ "FROM TBL_STAFF TNA "
				+ "LEFT JOIN TBL_ORGANIZATION ORG ON ORG.ORGID = TNA.ORGID "
				+ "WHERE 1=1 ");
			if(null != orgid){
				sb.append(" AND TNA.ORGID = "+orgid);
			}
		
		sb.append(" ORDER BY TNA.STAFFID DESC) T1 WHERE ROWNUM <= "+(pageInfo.getCurrentRecord()+pageInfo.getPageSize())+" ) T2 WHERE T2.RN > "+pageInfo.getCurrentRecord());
		return sb.toString();
	}
	
	public String selectTreeCountByPageInfo(PageInfo<TblStaff> pageInfo,Integer orgid) {
		StringBuffer sb = new StringBuffer("SELECT COUNT(0) "
				+ "FROM TBL_STAFF TNA "
				+ " LEFT JOIN TBL_ORGANIZATION ORG ON ORG.ORGID = TNA.ORGID "
				+ "WHERE 1=1 ");
		
		if(null != orgid){
			sb.append(" AND TNA.ORGID = "+orgid);
		}
		
		return sb.toString();
	}
}
